package net.hn.hnms.biz.intelligence.service;

import lombok.RequiredArgsConstructor;
import net.hn.hnms.biz.intelligence.vo.AlarmCountByLevelVO;
import net.hn.hnms.biz.intelligence.vo.AlarmCountBySystemVO;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.math.BigInteger;
import java.util.List;
import java.util.Map;

@Service
@RequiredArgsConstructor
public class SafetyIndexServiceImpl implements SafetyIndexService{

    @Qualifier("testJdbcTemplate")
    private final JdbcTemplate testJdbcTemplate;

    @Qualifier("supportJdbcTemplate")
    private final JdbcTemplate supportJdbcTemplate;

    @Override
    public AlarmCountByLevelVO alarmCountByLevel() {
        // 安全监控
        String sql = "select count(1) as count, alarmLevel as alarm_level from \n" +
                "( \n" +
                "SELECT  \n" +
                "  CASE sensor_type \n" +
                "   WHEN  '0004'  \n" +
                "   then \n" +
                "    case  \n" +
                "     when  extract(epoch FROM ( end_time- begin_time ))<=900  \n" +
                "     then  \n" +
                "      case  \n" +
                "       when (max_value<50 and max_value>=24) then '蓝色预警' \n" +
                "       when (max_value<90 and max_value>=50) then '蓝色预警' \n" +
                "       when (max_value<200 and max_value>=90) then '黄色预警' \n" +
                "       when ( max_value>=200) then '橙色预警'  \n" +
                "      end \n" +
                "     when extract(epoch FROM ( end_time- begin_time ))<=1500 and  extract(epoch FROM ( end_time- begin_time ))>900 \n" +
                "     then \n" +
                "      case \n" +
                "       WHEN (max_value<50 and max_value>=24) then '蓝色预警' \n" +
                "       when (max_value<90 and max_value>=50) then '黄色预警' \n" +
                "       when (max_value<200 and max_value>=90) then '黄色预警' \n" +
                "       when ( max_value>=200) then '橙色预警'  \n" +
                "      end \n" +
                "     when extract(epoch FROM ( end_time- begin_time ))<=1800 and  extract(epoch FROM ( end_time- begin_time ))>1500 \n" +
                "     then \n" +
                "      case \n" +
                "       WHEN (max_value<50 and max_value>=24) then '黄色预警' \n" +
                "       when (max_value<90 and max_value>=50) then '黄色预警' \n" +
                "       when (max_value<200 and max_value>=90) then '橙色预警' \n" +
                "       when ( max_value>=200) then '红色预警'  \n" +
                "      end \n" +
                "     when extract(epoch FROM ( end_time- begin_time ))>1800 \n" +
                "     then \n" +
                "      case \n" +
                "       WHEN (max_value<50 and max_value>=24) then '黄色预警' \n" +
                "       when (max_value<90 and max_value>=50) then '橙色预警' \n" +
                "       when (max_value<200 and max_value>=90) then '红色预警' \n" +
                "       when ( max_value>=200) then '红色预警'  \n" +
                "      end \n" +
                "    end \n" +
                "   WHEN  '0001'  \n" +
                "   then \n" +
                "    case  \n" +
                "     when  extract(epoch FROM ( end_time- begin_time ))<=900  \n" +
                "     then  \n" +
                "      case  \n" +
                "       when (max_value<1 and max_value>=0.5) then '蓝色预警' \n" +
                "       when (max_value<1.5 and max_value>=1) then '蓝色预警' \n" +
                "       when (max_value<2 and max_value>=1.5) then '黄色预警' \n" +
                "       when ( max_value>=2) then '橙色预警'  \n" +
                "      end \n" +
                "     when extract(epoch FROM ( end_time- begin_time ))<=1500 and  extract(epoch FROM ( end_time- begin_time ))>900 \n" +
                "     then \n" +
                "      case \n" +
                "       WHEN (max_value<1 and max_value>=0.5) then '蓝色预警' \n" +
                "       when (max_value<1.5 and max_value>=1) then '黄色预警' \n" +
                "       when (max_value<2 and max_value>=1.5) then '黄色预警' \n" +
                "       when ( max_value>=2) then '橙色预警'  \n" +
                "      end \n" +
                "     when extract(epoch FROM ( end_time- begin_time ))<=1800 and  extract(epoch FROM ( end_time- begin_time ))>1500 \n" +
                "     then \n" +
                "      case \n" +
                "       WHEN (max_value<1 and max_value>=0.5) then '黄色预警' \n" +
                "       when (max_value<1.5 and max_value>=1) then '黄色预警' \n" +
                "       when (max_value<2 and max_value>=1.5) then '橙色预警' \n" +
                "       when ( max_value>=2) then '红色预警'  \n" +
                "      end \n" +
                "     when extract(epoch FROM ( end_time- begin_time ))>1800 \n" +
                "     then \n" +
                "      case \n" +
                "       WHEN (max_value<1 and max_value>=0.5) then '黄色预警' \n" +
                "       when (max_value<1.5 and max_value>=1) then '橙色预警' \n" +
                "       when (max_value<2 and max_value>=1.5) then '红色预警' \n" +
                "       when ( max_value>=2) then '红色预警'  \n" +
                "      end \n" +
                "    end \n" +
                "  END as alarmLevel, \n" +
                "sensor_type,id \n" +
                "FROM     aqjk.dwd_aqjk_now_sensoralarm_count_stat  \n" +
                "where   (sensor_type = '0004' or  sensor_type='0001') and end_time is not null  and max_value  is not  null  \n" +
                "and (end_time>CURRENT_DATE  or begin_time>CURRENT_DATE ) and outlier_type='001'  \n" +
                ") t GROUP BY alarmLevel";
        List<Map<String, Object>> result = testJdbcTemplate.queryForList(sql);
        AlarmCountByLevelVO alarmCountByLevelVO = new AlarmCountByLevelVO(BigInteger.ZERO, BigInteger.ZERO, BigInteger.ZERO, BigInteger.ZERO);
        result.forEach(r -> {
            if (r.get("alarm_level") != null) {
                BigInteger count = (BigInteger) r.get("count");
                if (((String) r.get("alarm_level")).contains("红")) alarmCountByLevelVO.setAlarmCountRed(count);
                else if (((String) r.get("alarm_level")).contains("橙")) alarmCountByLevelVO.setAlarmCountOrange(count);
                else if (((String) r.get("alarm_level")).contains("黄")) alarmCountByLevelVO.setAlarmCountYellow(count);
                else if (((String) r.get("alarm_level")).contains("蓝")) alarmCountByLevelVO.setAlarmCountBlue(count);
            }
        });

        // 水害防治-长观空
        String sqlWater = "select count(1), warning_level as alarm_level from shfz.dwd_shfz_cgk_abnormal_holealarm dscah \n" +
                "where start_time > current_date or end_time > current_date \n" +
                "group by warning_level ";
        result = testJdbcTemplate.queryForList(sqlWater);
        result.forEach(r -> {
            if (r.get("alarm_level") != null) {
                BigInteger count = (BigInteger) r.get("count");
                if ((Integer) r.get("alarm_level") == 1) alarmCountByLevelVO.setAlarmCountRed(alarmCountByLevelVO.getAlarmCountRed().add(count));
                else if ((Integer) r.get("alarm_level") == 2) alarmCountByLevelVO.setAlarmCountOrange(alarmCountByLevelVO.getAlarmCountOrange().add(count));
                else if ((Integer) r.get("alarm_level") == 3) alarmCountByLevelVO.setAlarmCountYellow(alarmCountByLevelVO.getAlarmCountYellow().add(count));
                else if ((Integer) r.get("alarm_level") == 4) alarmCountByLevelVO.setAlarmCountBlue(alarmCountByLevelVO.getAlarmCountBlue().add(count));
            }
        });

        // 火灾防治
        String sqlFire = "select\n" +
                "\tcount(1)\n" +
                "from\n" +
                "\thzfz.dwd_sgjc_now_sensormonitor\n" +
                "where\n" +
                "\tdata_time > current_date\n" +
                "\tand point_status != 0";
        Integer c = testJdbcTemplate.queryForObject(sqlFire, Integer.class);
        // 不分红橙黄蓝，直接作为蓝色
        if (c != null && c > 0) {
            alarmCountByLevelVO.setAlarmCountBlue(alarmCountByLevelVO.getAlarmCountBlue().add(BigInteger.valueOf(c)));
        }
        // 顶板灾害-只有支架
        String sqlSupport = "select count(1) from zdsb.dwd_zdsb_his_support \n" +
                "where data_time >= CURRENT_DATE \n" +
                "and point_code like '%支架压力%' \n" +
                "and point_value >46 ";

        c = supportJdbcTemplate.queryForObject(sqlSupport, Integer.class);
        // 不分红橙黄蓝，直接作为蓝色
        if (c != null && c > 0) {
            alarmCountByLevelVO.setAlarmCountBlue(alarmCountByLevelVO.getAlarmCountBlue().add(BigInteger.valueOf(c)));
        }

        // 冲击地压
        String sqlBorehole = "select count(1) from cjdy.dwd_cjdy_zkyl_abnormal_boreholealarm where create_time >= CURRENT_DATE \n";

        c = testJdbcTemplate.queryForObject(sqlBorehole, Integer.class);
        // 不分红橙黄蓝，直接作为蓝色
        if (c != null && c > 0) {
            alarmCountByLevelVO.setAlarmCountBlue(alarmCountByLevelVO.getAlarmCountBlue().add(BigInteger.valueOf(c)));
        }

        // 粉尘
        String sqlDust = "select count(1) from fcfz.dwd_fcfz_now_sensormonitor where data_time >= CURRENT_DATE and point_code != '0'\n";
        c = testJdbcTemplate.queryForObject(sqlDust, Integer.class);
        // 不分红橙黄蓝，直接作为蓝色
        if (c != null && c > 0) {
            alarmCountByLevelVO.setAlarmCountBlue(alarmCountByLevelVO.getAlarmCountBlue().add(BigInteger.valueOf(c)));
        }
        return alarmCountByLevelVO;
    }

    @Override
    public AlarmCountBySystemVO alarmCountBySystem(String granularity) {
        AlarmCountBySystemVO alarmCountBySystemVO = new AlarmCountBySystemVO();


        //安全监控
        String sql = "select count(1) from aqjk.dwd_aqjk_his_sensoralarm " +
                " where begin_time + interval '1 " + granularity + "'>= current_date";
        Integer c = testJdbcTemplate.queryForObject(sql, Integer.class);
        alarmCountBySystemVO.setAqjkAlarmCount(c);

        //水灾-长观孔
        sql = "select count(1) from shfz.dwd_shfz_cgk_abnormal_holealarm dscah " +
                " where start_time + interval '1 " + granularity + "'>= current_date";
        c = testJdbcTemplate.queryForObject(sql, Integer.class);
        alarmCountBySystemVO.setShfzAlarmCount(c);

        //火灾 - todo 没有历史表
        sql = "select count(1) from hzfz.dwd_sgjc_now_sensormonitor where data_time + interval '1 " + granularity + "'>= current_date and point_status != '0' ";
        c = testJdbcTemplate.queryForObject(sql, Integer.class);
        alarmCountBySystemVO.setHzfzAlarmCount(c);

        // 顶板灾害 -顶板
        sql = "select count(1) from zdsb.dwd_zdsb_his_support \n" +
                "where data_time + interval ' 1 " + granularity + "'>= CURRENT_DATE \n" +
                "and point_code like '%支架压力%' \n" +
                "and point_value >46 ";
        c = supportJdbcTemplate.queryForObject(sql, Integer.class);
        alarmCountBySystemVO.setDbzhAlarmCount(c);

        // 冲击地压
        sql = "select count(1) from cjdy.dwd_cjdy_zkyl_abnormal_boreholealarm where create_time + interval '1 " + granularity + "'>= CURRENT_DATE";
        c = testJdbcTemplate.queryForObject(sql, Integer.class);
        alarmCountBySystemVO.setCjdyAlarmCount(c);

        // 粉尘
        sql = "select count(1) from fcfz.dwd_fcfz_now_sensormonitor where data_time + interval '1 " + granularity + "' >= CURRENT_DATE and point_code != '0' ";
        c = testJdbcTemplate.queryForObject(sql, Integer.class);
        alarmCountBySystemVO.setFcfzAlarmCount(c);

        return alarmCountBySystemVO;
    }

}
